package cn.com.headfree.tjfx.controller;

import cn.com.headfree.base.controller.ExBaseController;
import cn.com.headfree.base.controller.ResultData;
import cn.com.headfree.tjfx.service.IAnalysisService;
import cn.com.headfree.tjfx.vo.zjrcVo;
import cn.com.headfree.tjfx.vo.zyzgqkVo;
import cn.com.headfree.userinfo.service.IUserInfoService;
import cn.com.headfree.util.JsonUtil;
import cn.com.headfree.util.SysDictionaryUtil;
import cn.com.headfree.vo.SysDictionaryInfoVo;
import cn.hutool.core.date.DateUtil;
import cn.hutool.core.io.IoUtil;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;
import cn.hutool.poi.excel.StyleSet;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import com.gta.edu.sdk.common.dto.Page;
import org.apache.commons.lang.StringUtils;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.*;

//统计分析
@RestController
@RequestMapping(value = "/tjfx", produces = "application/json;charset=UTF-8")
public class AnalysisController extends ExBaseController {

	private static final Logger LOG = LogManager.getLogger(AnalysisController.class);
	@Autowired
	public IUserInfoService userInfoService;
	@Autowired
	public IAnalysisService analysisService;

	/**
	 * 专家人才-柱状图
	 */
	@RequestMapping("/getZjrcfbt")
	public ResultData getZjrcfbt(HttpServletRequest request) {
		ResultData rs = new ResultData();
		try {
		//专家人才字典
		List<SysDictionaryInfoVo> sysDictionaryInfoVos = userInfoService.getAttendanceUserTypeByValue("ExpertLevel");
		//国网安徽省电力有限公司宣城供电公司:10347258
		List<HashMap> list1 = analysisService.getChildOrgDeptByid("10347258");
		List<HashMap> list2 = analysisService.getZjrcfbtListByOrgId("10347258");
		List<String> legend = new ArrayList<>();
		List<String> xAxisData = new ArrayList<>();
		for(HashMap s:list1){
			xAxisData.add(s.get("name").toString());
		}
		List<HashMap> series = new ArrayList<>();
		for (SysDictionaryInfoVo d:sysDictionaryInfoVos) {
			legend.add(d.getName());
			HashMap map = new HashMap();
			map.put("name",d.getName());
			List<HashMap> dataList = new ArrayList<>();
			for(HashMap m:list1){
				HashMap m2 = new HashMap();
				m2.put("code",m.get("id").toString());
				int value=0;
				for(int i=0;i<list2.size();i++){
					String levelName = (null!=list2.get(i).get("level_name")?list2.get(i).get("level_name")+"":"");
					String orgId = (null!=list2.get(i).get("org_id")?list2.get(i).get("org_id")+"":"");
					String dic_d_value = d.getValue();
					String m_id = m.get("id").toString();
					if(StringUtils.isNotBlank(levelName)&&levelName.equals(dic_d_value)&&orgId.equals(m_id)){
						String allnum = list2.get(i).get("allnum") + "";
						value = Integer.valueOf(allnum);
						break;
					}
				}
				m2.put("value",value);
				dataList.add(m2);
			}
			map.put("data",dataList);
			series.add(map);
		}
		HashMap hashMap = new HashMap();
		hashMap.put("legend",legend);
		hashMap.put("xAxisData",xAxisData);
		hashMap.put("series",series);
		rs.setData(JsonUtil.toJson(hashMap));
		rs.setSuccess(true);
		}catch (Exception e){
			e.printStackTrace();
			rs.setSuccess(false);
		}
		return rs;
	}


	/**
	 * 专家人才列表
	 */
	@RequestMapping("/getZjrcDetailList")
	public ResultData getZjrcDetailList(Page<zjrcVo> page, @RequestBody zjrcVo zjrc) {
		ResultData rs = new ResultData();
		try {
			analysisService.findZjrcList(page, "10347258", zjrc);
			//从redis中获取字典数据
			Map<String, List<SysDictionaryInfoVo>> map = SysDictionaryUtil.getSysDictionaryList();
			if (!page.getResult().isEmpty()) {
				page.getResult().forEach(p -> {
					p.setSexName(getNameByValue("Gender", p.getSex(),map));// 性别
					p.setEducNameName(getNameByValue("PersonnelQualifications", p.getEducName(),map));// 学历名称
					p.setMajorName(getNameByValue("ProWorkTypes", p.getMajor(),map));// 专业
					p.setEmpTitleName(getNameByValue("TalentTitle", p.getEmpTitle(),map));// 人才称号名称
					p.setLevelNameName(getNameByValue("ExpertLevel", p.getLevelName(),map));// 专家等级
					p.setTypeName(getNameByValue("ExpertCategory", p.getType(),map));// 类别
				});
			}
			rs.setData(page);
			LOG.info("查询专家人才列表成功！");
		} catch (Exception e) {
			rs.setSuccess(false);
			rs.setErrorMsg("查询专家人才列表失败！");
			e.printStackTrace();
		}
		return rs;
	}

	/**
	 * 专家人才列表导出
	 */
	@RequestMapping("/exportZjrcDetailExcel")
	public void exportZjrcDetailExcel(HttpServletRequest request, HttpServletResponse response, zjrcVo zjrc) {
		try {
			List<zjrcVo> list = analysisService.findZjrcList("10347258", zjrc);
			//从redis中获取字典数据
			Map<String, List<SysDictionaryInfoVo>> map = SysDictionaryUtil.getSysDictionaryList();
			list.stream().forEach(p -> {
				p.setSexName(getNameByValue("Gender", p.getSex(),map));// 性别
				p.setEducNameName(getNameByValue("PersonnelQualifications", p.getEducName(),map));// 学历名称
				p.setMajorName(getNameByValue("ProWorkTypes", p.getMajor(),map));// 专业
				p.setEmpTitleName(getNameByValue("TalentTitle", p.getEmpTitle(),map));// 人才称号名称
				p.setLevelNameName(getNameByValue("ExpertLevel", p.getLevelName(),map));// 专家等级
				p.setTypeName(getNameByValue("ExpertCategory", p.getType(),map));// 类别
			});

			// 通过工具类创建writer，默认创建xls格式
			ExcelWriter writer = ExcelUtil.getBigWriter();
			//自定义标题别名
			writer.addHeaderAlias("erpNo", "ERP编号");
			writer.addHeaderAlias("userName", "姓名");
			writer.addHeaderAlias("orgName", "工作单位");
			writer.addHeaderAlias("sexName", "性别");
			writer.addHeaderAlias("birthDate", "出生日期");
			writer.addHeaderAlias("majorName", "专业");
			writer.addHeaderAlias("empTitleName", "人才称号名称");
			writer.addHeaderAlias("acquTime", "取得时间");
			writer.addHeaderAlias("levelNameName", "专家等级");
			writer.addHeaderAlias("typeName", "类别");
			writer.addHeaderAlias("isContEducName", "是否全日制");
			writer.addHeaderAlias("educNameName", "学历名称");
			// 合并单元格后的标题行，使用默认标题样式
			writer.merge(11, "专家人才一览表");
			//设置单元格宽度
			writer.setColumnWidth(2,35);
			writer.setColumnWidth(4,18);
			writer.setColumnWidth(5,20);
			writer.setColumnWidth(6,25);
			writer.setColumnWidth(7,18);
			writer.setColumnWidth(8,15);
			writer.setColumnWidth(10,12);
			writer.setColumnWidth(11,20);

//			writer.renameSheet(0,"用户登录信息");
			//设置只导出有别名的字段
			writer.setOnlyAlias(true);
			// 一次性写出内容，使用默认样式，强制输出标题
			writer.write(list, true);
			//out为OutputStream，需要写出到的目标流

			//response为HttpServletResponse对象
//			response.setContentType("application/vnd.ms-excel;charset=utf-8");//导出为.xls格式
			response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");//导出为.xlsx格式
			//name是下载对话框的名称，不支持中文，想用中文名称需要进行utf8编码
			String excelName = "专家人才";
			response.setHeader("Content-Disposition","attachment;filename=" + URLEncoder.encode(excelName + DateUtil.format(new Date(),"yyyyMMddHHmmss") + ".xlsx", "utf-8"));
			ServletOutputStream out= null;
			try {
				out = response.getOutputStream();
				writer.flush(out, true);
			} catch (IOException e) {
				e.printStackTrace();
			}finally {
				// 关闭writer，释放内存
				writer.close();
			}
			//此处记得关闭输出Servlet流
			IoUtil.close(out);

		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/**
	 * 首页信息总览-专家人才-柱状图
	 */
	@RequestMapping("/getPreViewZjrcfbt")
	public ResultData getPreViewZjrcfbt(HttpServletRequest request) {
		ResultData rs = new ResultData();
		try {
			//专家人才字典
			List<SysDictionaryInfoVo> sysDictionaryInfoVos = userInfoService.getAttendanceUserTypeByValue("ExpertLevel");
			List<HashMap> list2 = analysisService.getPreViewZjrcfbtListByOrgId("10347258");
			List<String> yAxis_data = new ArrayList<>();
			List<HashMap> series = new ArrayList<>();
			for (SysDictionaryInfoVo d:sysDictionaryInfoVos) {
				yAxis_data.add(d.getName());
				HashMap map = new HashMap();
				map.put("name",d.getName());
				String dic_d_value = d.getValue();
				int value=0;
				for(int i=0;i<list2.size();i++){
					String levelName = (null!=list2.get(i).get("level_name")?list2.get(i).get("level_name")+"":"");
					if(StringUtils.isNotBlank(levelName)&&levelName.equals(dic_d_value)){
						String allnum = list2.get(i).get("allnum") + "";
						value = Integer.valueOf(allnum);
						break;
					}
				}
				map.put("value",value);
				series.add(map);
			}
			HashMap hashMap = new HashMap();
			hashMap.put("yAxis_data",yAxis_data);
			hashMap.put("series",series);
			rs.setData(JsonUtil.toJson(hashMap));
			rs.setSuccess(true);
		}catch (Exception e){
			e.printStackTrace();
			rs.setSuccess(false);
		}
		return rs;
	}


	/**
	 * 首页信息总览-执业资格-柱状图
	 */
	@RequestMapping("/getPreViewZyzgqkfbt")
	public ResultData getPreViewZyzgqkfbt(HttpServletRequest request) {
		ResultData rs = new ResultData();
		try {
			//专业技术资格等级字典
			List<SysDictionaryInfoVo> sysDictionaryInfoVos = userInfoService.getAttendanceUserTypeByValue("ProfessionalQualifications");
			List<HashMap> list2 = analysisService.getPreViewZyzgqkListByOrgId("10347258");
			List<String> yAxis_data = new ArrayList<>();
			List<HashMap> series = new ArrayList<>();
			for (SysDictionaryInfoVo d:sysDictionaryInfoVos) {
				yAxis_data.add(d.getName());
				HashMap map = new HashMap();
				map.put("name",d.getName());
				String dic_d_value = d.getValue();
				int value=0;
				for(int i=0;i<list2.size();i++){
					String levelName = (null!=list2.get(i).get("subj_level")?list2.get(i).get("subj_level")+"":"");
					if(StringUtils.isNotBlank(levelName)&&levelName.equals(dic_d_value)){
						String allnum = list2.get(i).get("allnum") + "";
						value = Integer.valueOf(allnum);
						break;
					}
				}
				map.put("value",value);
				series.add(map);
			}
			HashMap hashMap = new HashMap();
			hashMap.put("yAxis_data",yAxis_data);
			hashMap.put("series",series);
			rs.setData(JsonUtil.toJson(hashMap));
			rs.setSuccess(true);
		}catch (Exception e){
			e.printStackTrace();
			rs.setSuccess(false);
		}
		return rs;
	}

	/**
	 * 执业资格情况-柱状图
	 */
	@RequestMapping("/getZyzgqkfbt")
	public ResultData getZyzgqkfbt(HttpServletRequest request) {
		ResultData rs = new ResultData();
		try {
		//专业技术资格等级字典
		List<SysDictionaryInfoVo> sysDictionaryInfoVos = userInfoService.getAttendanceUserTypeByValue("ProfessionalQualifications");
		//国网安徽省电力有限公司宣城供电公司:10347258
		List<HashMap> list1 = analysisService.getChildOrgDeptByid("10347258");
		List<HashMap> list2 = analysisService.getZyzgqkListByOrgId("10347258");
		List<String> legend = new ArrayList<>();
		List<String> xAxisData = new ArrayList<>();
		for(HashMap s:list1){
			xAxisData.add(s.get("name").toString());
		}
		List<HashMap> series = new ArrayList<>();
		for (SysDictionaryInfoVo d:sysDictionaryInfoVos) {
			legend.add(d.getName());
			HashMap map = new HashMap();
			map.put("name",d.getName());
			List<HashMap> dataList = new ArrayList<>();
			for(HashMap m:list1){
				HashMap m2 = new HashMap();
				m2.put("code",m.get("id").toString());
				int value=0;
				for(int i=0;i<list2.size();i++){
//					if(null!=list2.get(i).get("subj_level")&&list2.get(i).get("subj_level")==d.getValue()&&list2.get(i).get("org_id")==m.get("id").toString()){
					String levelName = (null!=list2.get(i).get("subj_level")?list2.get(i).get("subj_level")+"":"");
					String orgId = (null!=list2.get(i).get("org_id")?list2.get(i).get("org_id")+"":"");
					String dic_d_value = d.getValue();
					String m_id = m.get("id").toString();
					if(StringUtils.isNotBlank(levelName)&&levelName.equals(dic_d_value)&&orgId.equals(m_id)){
						String allnum = list2.get(i).get("allnum") + "";
						value = Integer.valueOf(allnum);
						break;
					}
				}
				m2.put("value",value);
				dataList.add(m2);
			}
			map.put("data",dataList);
			series.add(map);
		}
		HashMap hashMap = new HashMap();
		hashMap.put("legend",legend);
		hashMap.put("xAxisData",xAxisData);
		hashMap.put("series",series);
		rs.setData(JsonUtil.toJson(hashMap));
		rs.setSuccess(true);
		}catch (Exception e){
			e.printStackTrace();
			rs.setSuccess(false);
		}
		return rs;
	}


	/**
	 * 执业资格情况列表
	 */
	@RequestMapping("/getZyzgqkDetailList")
	public ResultData getZyzgqkDetailList(Page<zyzgqkVo> page, @RequestBody zyzgqkVo zyzgqk) {
		ResultData rs = new ResultData();
		try {
			analysisService.findZyzgqkList(page, "10347258", zyzgqk);
			//从redis中获取字典数据
			Map<String, List<SysDictionaryInfoVo>> map = SysDictionaryUtil.getSysDictionaryList();
			if (!page.getResult().isEmpty()) {
				page.getResult().forEach(p -> {
					p.setSexName(getNameByValue("Gender", p.getSex(),map));// 性别
					p.setEducNameName(getNameByValue("PersonnelQualifications", p.getEducName(),map));// 学历名称
					p.setSubjSetName(getNameByValue("ProTechnicalQualifications", p.getSubjSet(),map));// 专业技术资格系列
					p.setSubjLevelName(getNameByValue("ProfessionalQualifications", p.getSubjLevel(),map));// 专业技术资格等级
					p.setOrgLevelName(getNameByValue("IdentificationUnitLevel", p.getOrgLevel(),map));// 发证单位级别
				});
			}
			rs.setData(page);
			LOG.info("查询执业资格情况列表成功！");
		} catch (Exception e) {
			rs.setSuccess(false);
			rs.setErrorMsg("查询执业资格情况列表失败！");
			e.printStackTrace();
		}
		return rs;
	}

	/**
	 * 执业资格情况列表导出
	 */
	@RequestMapping("/exportZyzgqkDetailExcel")
	public void exportZyzgqkDetailExcel(HttpServletRequest request, HttpServletResponse response, zyzgqkVo zyzgqk) {
		try {
			List<zyzgqkVo> list = analysisService.findZyzgqkList("10347258", zyzgqk);
			//从redis中获取字典数据
			Map<String, List<SysDictionaryInfoVo>> map = SysDictionaryUtil.getSysDictionaryList();
			list.stream().forEach(p -> {
				p.setSexName(getNameByValue("Gender", p.getSex(),map));// 性别
				p.setEducNameName(getNameByValue("PersonnelQualifications", p.getEducName(),map));// 学历名称
				p.setSubjSetName(getNameByValue("ProTechnicalQualifications", p.getSubjSet(),map));// 专业技术资格系列
				p.setSubjLevelName(getNameByValue("ProfessionalQualifications", p.getSubjLevel(),map));// 专业技术资格等级
				p.setOrgLevelName(getNameByValue("IdentificationUnitLevel", p.getOrgLevel(),map));// 发证单位级别
			});

			// 通过工具类创建writer，默认创建xls格式
			ExcelWriter writer = ExcelUtil.getBigWriter();
			//自定义标题别名
			writer.addHeaderAlias("erpNo", "ERP编号");
			writer.addHeaderAlias("userName", "姓名");
			writer.addHeaderAlias("orgName", "工作单位");
			writer.addHeaderAlias("sexName", "性别");
			writer.addHeaderAlias("birthDate", "出生日期");
			writer.addHeaderAlias("acquTime", "取得时间");
			writer.addHeaderAlias("subjName", "专业技术资格名称");
			writer.addHeaderAlias("subjSetName", "专业技术资格系列");
			writer.addHeaderAlias("subjLevelName", "专业技术资格等级");
			writer.addHeaderAlias("auditOrg", "发证单位");
			writer.addHeaderAlias("orgLevelName", "发证单位级别");
			writer.addHeaderAlias("certNo", "证书编号");
			writer.addHeaderAlias("isContEducName", "是否全日制");
			writer.addHeaderAlias("educNameName", "学历名称");
			// 合并单元格后的标题行，使用默认标题样式
			writer.merge(13, "执业资格情况一览表");
			//设置单元格宽度
			writer.setColumnWidth(2,35);
			writer.setColumnWidth(4,18);
			writer.setColumnWidth(5,18);
			writer.setColumnWidth(6,20);
			writer.setColumnWidth(7,20);
			writer.setColumnWidth(8,20);
			writer.setColumnWidth(10,15);
			writer.setColumnWidth(13,20);

//			writer.renameSheet(0,"用户登录信息");
			//设置只导出有别名的字段
			writer.setOnlyAlias(true);
			// 一次性写出内容，使用默认样式，强制输出标题
			writer.write(list, true);
			//out为OutputStream，需要写出到的目标流

			//response为HttpServletResponse对象
//			response.setContentType("application/vnd.ms-excel;charset=utf-8");//导出为.xls格式
			response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");//导出为.xlsx格式
			//name是下载对话框的名称，不支持中文，想用中文名称需要进行utf8编码
			String excelName = "执业资格情况";
			response.setHeader("Content-Disposition","attachment;filename=" + URLEncoder.encode(excelName + DateUtil.format(new Date(),"yyyyMMddHHmmss") + ".xlsx", "utf-8"));
			ServletOutputStream out= null;
			try {
				out = response.getOutputStream();
				writer.flush(out, true);
			} catch (IOException e) {
				e.printStackTrace();
			}finally {
				// 关闭writer，释放内存
				writer.close();
			}
			//此处记得关闭输出Servlet流
			IoUtil.close(out);

		} catch (Exception e) {
			e.printStackTrace();
		}
	}


	/**
	 * 获取单位
	 */
	@RequestMapping("/getOrgInfo")
	public ResultData getOrgInfo(HttpServletRequest request) {
		ResultData rs = new ResultData();
		try {
			//国网安徽省电力有限公司宣城供电公司:10347258
			List<HashMap> list1 = analysisService.getChildOrgDeptByid("10347258");
			rs.setData(list1);
			rs.setSuccess(true);
		}catch (Exception e){
			e.printStackTrace();
			rs.setSuccess(false);
		}
		return rs;
	}

	/**
	 * 获取name值
	 *
	 * @param name
	 * @param dictValue
	 * @return
	 */
	public static String getNameByValue(String name, String dictValue,Map<String, List<SysDictionaryInfoVo>> map) {
		List<SysDictionaryInfoVo> entityList = map.get(name);
		String dictName = "";
		if (dictValue != null) {
			if (entityList != null && entityList.size() > 0) {
				for (int i = 0; i < entityList.size(); i++) {
					SysDictionaryInfoVo entity = JSONObject.parseObject(JSON.toJSONString(entityList.get(i)), SysDictionaryInfoVo.class);
					if (dictValue.equals(entity.getValue())) {
						dictName = entity.getName();
						return dictName;
					}
				}
			}
		}
		return dictValue;
	}


}
